﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace WebApplication1
{
    public class SQLHelper
    {
        public SQLHelper()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            string connStr = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            string connStr = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    return cmd.ExecuteScalar();
                }
            }
        }//object i = SQLHelper.ExecuteScalar("select count(*) from Port_Emp");

        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
        {
            string connStr = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    DataSet m_dataset = new DataSet();
                    SqlDataAdapter m_adapter = new SqlDataAdapter(cmd);
                    m_adapter.Fill(m_dataset);
                    return m_dataset.Tables[0];
                }
            }
        }

        public static int InsertSchool(string Name, string Address, float Longitude, float Latitude, string Type, string Phone, string BriefInfo)
        {
            return SQLHelper.ExecuteNonQuery("insert into T_School(Name,Address,Longitude,Latitude,Type,Phone,BriefInfo) values(@Name,@Address,@Longitude,@Latitude,@Type,@phone,@BriefInfo)", new SqlParameter("Name", Name), new SqlParameter("Address", Address), new SqlParameter("Longitude", Longitude), new SqlParameter("Latitude", Latitude), new SqlParameter("Type", Type), new SqlParameter("Phone", Phone), new SqlParameter("BriefInfo", BriefInfo));
        }

        public static int UpdateSchoolById(int Id, string Name, string Address, float Longitude, float Latitude, string Type, string Phone, string BriefInfo)
        {
            return SQLHelper.ExecuteNonQuery("update T_School set Name=@Name,Address=@Address,Longitude=@Longitude,Latitude=@Latitude,Type=@Type,Phone=@Phone,BriefInfo=@BriefInfo where Id=@Id", new SqlParameter("Name", Name), new SqlParameter("Address", Address), new SqlParameter("Longitude", Longitude), new SqlParameter("Latitude", Latitude), new SqlParameter("Type", Type), new SqlParameter("Id", Id), new SqlParameter("Phone", Phone), new SqlParameter("BriefInfo", BriefInfo));
        }

        public static int deleteSchoolById(int Id)
        {
            return SQLHelper.ExecuteNonQuery("delete from T_School where Id=@Id", new SqlParameter("Id", Id));
        }

        public static void getSchools(out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select * from T_School");
        }

        public static void getSchoolInfoById(int Id, out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select Id,Name,Address,Longitude,Latitude,Phone,Popularity,round(SL_Integrate,0) as SL_Integrate,BriefInfo,Type,round(SL_Quality,0) as SL_Quality,round(SL_Price,0) as SL_Price,round(SL_Service,0) as SL_Service,round(SL_Environment,0) as SL_Environment from T_School where Id=@Id", new SqlParameter("Id", Id));
        }

        public static int getMaxSchoolId()
        {
            return (int)(SQLHelper.ExecuteScalar("select max(Id) from T_School"));
        }

        public static int AddTagBySchoolId(int SchoolId, int TagId)
        {
            return SQLHelper.ExecuteNonQuery("insert into T_Tag(SchoolId,TagId) values(@SchoolId,@TagId)", new SqlParameter("SchoolId", SchoolId), new SqlParameter("TagId", TagId));
        }

        public static int deleteTagBySchoolId(int SchoolId)
        {
            return SQLHelper.ExecuteNonQuery("delete from T_Tag where SchoolId=@SchoolId", new SqlParameter("SchoolId", SchoolId));
        }

        public static int deleteTagInfoByTagId(int Id)
        {
            return SQLHelper.ExecuteNonQuery("delete from T_TagInfo where Id=@Id", new SqlParameter("Id", Id));
        }

        public static void getTagsBySchoolId(int SchoolId, out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select T_TagInfo.Id, T_TagInfo.TagName, T_TagInfo.ParentId from T_Tag inner join T_TagInfo on T_Tag.TagId = T_TagInfo.Id WHERE  (T_Tag.SchoolId = @SchoolId)", new SqlParameter("SchoolId", SchoolId));
        }

        public static void getSchoolsByTagsId(int TagId, out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select * from T_School inner join T_Tag on T_School.Id=T_Tag.SchoolId where T_Tag.TagId=@TagId", new SqlParameter("TagId", TagId));
        }



        public static void getTagInfo(out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select * from T_TagInfo");
        }

        public static int AddTag(string TagName, int ParentId, int TagLevel)
        {
            return SQLHelper.ExecuteNonQuery("insert into T_TagInfo(TagName,ParentId,TagLevel) values(@TagName,@ParentId,@TagLevel)", new SqlParameter("TagName", TagName), new SqlParameter("ParentId", ParentId), new SqlParameter("TagLevel", TagLevel));

        }

        public static void getRootTags(out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select * from T_TagInfo where ParentId=0");
        }

        public static void getRootTagsName(out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select TagName from T_TagInfo where ParentId=0");
        }

        public static void getChildTagsByParentId(int ParentId, out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select * from T_TagInfo where ParentId=@ParentId", new SqlParameter("ParentId", ParentId));
        }

        public static void getLeafTagsBySchoolId(int SchoolId, out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select * from T_TagInfo inner join T_Tag on T_TagInfo.Id=T_Tag.TagId where T_Tag.SchooolId=@SchoolId and T_TagInfo.Level=3", new SqlParameter("SchoolId", SchoolId));
        }

        public static int ExecuteLogin(string input_UserName, string input_Password, out DataTable output_AccountInfo)
        {
            output_AccountInfo = null;
            output_AccountInfo = SQLHelper.ExecuteDataTable("select * from T_Account where UserName=@UserName", new SqlParameter("UserName", input_UserName));
            if (output_AccountInfo.Rows.Count <= 0)
                return -1;//用户名不存在
            DataRow row = output_AccountInfo.Rows[0];
            string dbPassword = Convert.ToString(row["Password"]);
            if (dbPassword != input_Password)
                return 0;//密码不匹配
            return 1;

        }

        public static int RegUserAccount(string UserName, string Password, string Email)
        {
            int i = SQLHelper.ExecuteNonQuery("insert into T_Account(UserName, Password, Type) values(@UserName, @Password, 'User')", new SqlParameter("UserName", UserName), new SqlParameter("Password", Password));
            if (i != 1)
                return 0;
            int PK_Account = (Int32)SQLHelper.ExecuteScalar("select Id from T_Account where UserName=@UserName", new SqlParameter("UserName", UserName));
            SQLHelper.ExecuteNonQuery("insert into T_UserAccountInfo(PK_Account,Email)values(@PK_Account,@Email)", new SqlParameter("PK_Account", PK_Account), new SqlParameter("Email", Email));
            return i;
        }

        public static int RegSchoolAccount(string UserName, string Password, string Email)
        {
            if(SQLHelper.getAccountCountByUserName(UserName) > 0)
            {
                return 0;
            }
            
            int i = SQLHelper.ExecuteNonQuery("insert into T_Account(UserName, Password, Type) values(@UserName, @Password, 'School')", new SqlParameter("UserName", UserName), new SqlParameter("Password", Password));
            if (i != 1)
                return 0;
            int PK_Account = (Int32)SQLHelper.ExecuteScalar("select Id from T_Account where UserName=@UserName", new SqlParameter("UserName", UserName));
            SQLHelper.ExecuteNonQuery("insert into T_SchoolAccountInfo(PK_Account,Email) values (@PK_Account,@Email)", new SqlParameter("PK_Account", PK_Account), new SqlParameter("Email", Email));
            return i;
        }

        public static int ChangeAccountPasswordById(int Id, string Password)
        {
            return SQLHelper.ExecuteNonQuery("update T_Account set Password=@Password where Id=@Id", new SqlParameter("Password", Password), new SqlParameter("Id", Id));
        }

        public static int ChangeUserAccountEmailById(int PK_Account, string Email)
        {
            return SQLHelper.ExecuteNonQuery("update T_UserAccountInfo set Email=@Email where PK_Account=@PK_Account", new SqlParameter("Email", Email), new SqlParameter("PK_Account", PK_Account));
        }

        public static int ChangeSchoolAccountEmailById(int PK_Account, string Email)
        {
            return SQLHelper.ExecuteNonQuery("update T_SchoolAccountInfo set Email=@Email where PK_Account=@PK_Account", new SqlParameter("Email", Email), new SqlParameter("PK_Account", PK_Account));
        }

        public static string getSchoolEmailByAccountId(int PK_Account)
        {
            return SQLHelper.ExecuteScalar("select Email from T_SchoolAccountInfo where PK_Account=@PK_Account", new SqlParameter("PK_Account", PK_Account)).ToString();
        }

        public static string getUserEmailByUserAccountId(int PK_Account)
        {
            return SQLHelper.ExecuteScalar("select Email from T_UserAccountInfo where PK_Account=@PK_Account", new SqlParameter("PK_Account", PK_Account)).ToString();
        }

        

        public static int getAccountCountByUserName(string UserName)
        {
            return (Int32)SQLHelper.ExecuteScalar("select count(*)from T_Account where UserName=@UserName", new SqlParameter("UserName", UserName));
        }

        public static int getUserAccountCountByEmail(string Email)
        {
            return (Int32)SQLHelper.ExecuteScalar("select count(*)from T_UserAccountInfo where Email=@Email", new SqlParameter("Email", Email));
        }

        public static int getSchoolAccountCountByEmail(string Email)
        {
            return (Int32)SQLHelper.ExecuteScalar("select count(*)from T_SchoolAccountInfo where Email=@Email", new SqlParameter("Email", Email));
        }

        public static void getFileUpload(out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select T_FileUpload.Id, T_TagInfo.TagName,T_FileUpload.OriginName,T_Account.UserName,T_FileUpload.UploadTime from T_TagInfo,T_FileUpload,T_Account where T_Account.Id=T_FileUpload.PK_Account and T_TagInfo.Id=T_FileUpload.PK_TagInfo order by T_TagInfo.TagName");
        }

        public static void getFileUploadBelongTagName(out DataTable dt, string TagName)
        {
            dt = SQLHelper.ExecuteDataTable("select T_FileUpload.Id, T_TagInfo.TagName,T_FileUpload.OriginName,T_Account.UserName,T_FileUpload.UploadTime from T_TagInfo,T_FileUpload,T_Account where T_Account.Id=T_FileUpload.PK_Account and T_TagInfo.Id=T_FileUpload.PK_TagInfo and T_TagInfo.TagName=@TagName", new SqlParameter("TagName", TagName));
        }

        public static void getTopFileUploadBelongTagName(out DataTable dt, string TagName, int count)
        {
            dt = SQLHelper.ExecuteDataTable("select top " + count.ToString() + " T_FileUpload.Id, T_TagInfo.TagName,T_FileUpload.OriginName,T_Account.UserName,T_FileUpload.UploadTime from T_TagInfo,T_FileUpload,T_Account where T_Account.Id=T_FileUpload.PK_Account and T_TagInfo.Id=T_FileUpload.PK_TagInfo and T_TagInfo.TagName=@TagName order by T_FileUpload.UploadTime desc", new SqlParameter("TagName", TagName));
        }

        public static string getFileNameById(int Id)
        {
            return SQLHelper.ExecuteScalar("select Name from T_FileUpload where Id=@Id", new SqlParameter("Id", Id)).ToString();
        }

        public static string getOriginFileNameById(int Id)
        {
            return SQLHelper.ExecuteScalar("select OriginName from T_FileUpload where Id=@Id", new SqlParameter("Id", Id)).ToString();
        }

        public static int AddUploadFile(string Name, string UploadTime, string TagName, string UserName, string OriginName)
        {
            int PK_TagInfo = (Int32)SQLHelper.ExecuteScalar("select Id from T_TagInfo where TagName=@TagName and TagLevel=1", new SqlParameter("TagName", TagName));
            int PK_Account = (Int32)SQLHelper.ExecuteScalar("select Id from T_Account where UserName=@UserName", new SqlParameter("UserName", UserName));
            return SQLHelper.ExecuteNonQuery("insert into T_FileUpload(Name,UploadTime,PK_TagInfo,PK_Account,OriginName) values(@Name,@UploadTime,@PK_TagInfo,@PK_Account,@OriginName)", new SqlParameter("Name", Name), new SqlParameter("UploadTime", UploadTime), new SqlParameter("PK_TagInfo", PK_TagInfo), new SqlParameter("PK_Account", PK_Account), new SqlParameter("OriginName", OriginName));
        }

        public static int deleteFile(string Name)
        {
            return SQLHelper.ExecuteNonQuery("delete from T_FileUpload where Name=@Name", new SqlParameter("Name", Name));
        }

        public static int AddUploadFile(string Title, string NewsContent, string UploadTime, string UserName, int ViewCount)
        {
            int PK_Account = (Int32)SQLHelper.ExecuteScalar("select Id From T_Account where UserName=@UserName", new SqlParameter("UserName", UserName));
            return SQLHelper.ExecuteNonQuery("insert into T_News(Title,NewsContent,UploadTime,PK_Account,ViewCount)", new SqlParameter("Title", Title), new SqlParameter("NewsContent", NewsContent), new SqlParameter("UploadTime", UploadTime), new SqlParameter("PK_Account", PK_Account), new SqlParameter("ViewCount", ViewCount));
        }

        //////////////////////////////////////////////////////////////////////////////////////////////////////
        public static void getNews(out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select T_News.Id,T_News.Title,T_News.UploadTime,T_Account.UserName,T_TagInfo.TagName from T_News,T_Account,T_TagInfo where T_News.PK_Account=T_Account.Id and T_News.PK_TagInfo=T_TagInfo.Id order by T_TagInfo.TagName");
        }

        public static int deleteNewsById(int Id)
        {
            return SQLHelper.ExecuteNonQuery("delete from T_News where Id=@Id", new SqlParameter("Id", Id));
        }

        public static int updateNewsById(int Id, string Title, string NewsContent, string UploadTime, int PK_Account, int PK_TagInfo)
        {
            return SQLHelper.ExecuteNonQuery("update T_News set Title=@Title,NewsContent=@NewsContent,UploadTime=@UploadTime,PK_Account=@PK_Account,PK_TagInfo=@PK_TagInfo where Id=@Id", new SqlParameter("Title", Title), new SqlParameter("NewsContent", NewsContent), new SqlParameter("UploadTime", UploadTime), new SqlParameter("PK_Account", PK_Account), new SqlParameter("PK_TagInfo", PK_TagInfo), new SqlParameter("Id", Id));
        }

        public static int AddNews(string Title, string NewsContent, string UploadTime, int PK_Account, int PK_TagInfo)
        {
            return SQLHelper.ExecuteNonQuery("insert into T_News(Title,NewsContent,UploadTime,PK_Account,PK_TagInfo) values(@Title,@NewsContent,@UploadTime,@PK_Account,@PK_TagInfo)", new SqlParameter("Title", Title), new SqlParameter("NewsContent", NewsContent), new SqlParameter("UploadTime", UploadTime), new SqlParameter("PK_Account", PK_Account), new SqlParameter("PK_TagInfo", PK_TagInfo));
        }

        public static void getNewsBelongTagName(out DataTable dt, string TagName)
        {
            dt = SQLHelper.ExecuteDataTable("select T_News.Id, T_News.Title,T_News.UploadTime,T_Account.UserName,T_News.ViewCount from T_News,T_TagInfo,T_Account where T_Account.Id=T_News.PK_Account and T_TagInfo.Id=T_News.PK_TagInfo and T_TagInfo.TagName=@TagName", new SqlParameter("TagName", TagName));
        }

        public static void getNewsById(int Id, out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select T_News.Title,T_News.NewsContent,T_News.UploadTime,T_Account.UserName,T_News.ViewCount,T_News.PK_TagInfo from T_News,T_Account where T_News.Id=@Id and T_News.PK_Account=T_Account.Id", new SqlParameter("Id", Id));
        }

        public static void getTopNewsBelongTagName(out DataTable dt, string TagName, int count)
        {
            dt = SQLHelper.ExecuteDataTable("select top " + count.ToString() + " T_News.Id, T_News.Title,T_News.UploadTime,T_Account.UserName,T_News.ViewCount from T_News,T_TagInfo,T_Account where T_Account.Id=T_News.PK_Account and T_TagInfo.Id=T_News.PK_TagInfo and T_TagInfo.TagName=@TagName order by T_News.UploadTime desc", new SqlParameter("TagName", TagName));
        }

        public static void updateNewsViewCountById(int Id)
        {
            SQLHelper.ExecuteNonQuery("update T_News set ViewCount=ViewCount+1 where Id=@Id", new SqlParameter("Id", Id));
        }

        //参考书///////////////////////////////////////////////////////////////////////////////////////////

        public static void getBook(out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select Id,BookName,BookDescription,PK_TagInfo,UploadTime from T_Book order by PK_TagInfo");
        }

        public static int deleteBookById(int Id)
        {
            return SQLHelper.ExecuteNonQuery("delete from T_Book where Id=@Id", new SqlParameter("Id", Id));
        }

        public static int updateBookByIdWithImage(int Id, string BookName, byte[] BookImage, string BookUrl, string BookDescription, int PK_TagInfo, string UploadTime)
        {
            return SQLHelper.ExecuteNonQuery("update T_Book set BookName=@BookName,BookImage=@BookImage,BookUrl=@BookUrl,BookDescription=@BookDescription,PK_TagInfo=@PK_TagInfo,UploadTime=@UploadTime where Id=@Id", new SqlParameter("Id", Id), new SqlParameter("BookName", @BookName), new SqlParameter("BookImage", BookImage), new SqlParameter("BookUrl", BookUrl), new SqlParameter("BookDescription", BookDescription), new SqlParameter("PK_TagInfo", PK_TagInfo), new SqlParameter("UploadTime", UploadTime));
        }

        public static int updateBookByIdWithoutImg(int Id, string BookName, string BookUrl, string BookDescription, int PK_TagInfo, string UploadTime)
        {
            return SQLHelper.ExecuteNonQuery("update T_Book set BookName=@BookName,BookUrl=@BookUrl,BookDescription=@BookDescription,PK_TagInfo=@PK_TagInfo,UploadTime=@UploadTime where Id=@Id", new SqlParameter("Id", Id), new SqlParameter("BookName", @BookName), new SqlParameter("BookUrl", BookUrl), new SqlParameter("BookDescription", BookDescription), new SqlParameter("PK_TagInfo", PK_TagInfo), new SqlParameter("UploadTime", UploadTime));
        }

        public static int AddBook(string BookName, byte[] BookImage, string BookUrl, string BookDescription, int PK_TagInfo, string UploadTime)
        {
            return SQLHelper.ExecuteNonQuery("insert into T_Book(BookName,BookImage,BookUrl,BookDescription,PK_TagInfo,UploadTime)values(@BookName,@BookImage,@BookUrl,@BookDescription,@PK_TagInfo,@UploadTime)", new SqlParameter("BookName", BookName), new SqlParameter("BookImage", BookImage), new SqlParameter("BookUrl", BookUrl), new SqlParameter("BookDescription", BookDescription), new SqlParameter("PK_TagInfo", PK_TagInfo), new SqlParameter("UploadTime", UploadTime));
        }

        public static void getBookBelongTagName(out DataTable dt, string TagName)
        {
            dt = SQLHelper.ExecuteDataTable("select T_Book.Id,T_Book.BookName,T_Book.BookUrl,T_Book.BookDescription,T_Book.UploadTime from T_Book,T_TagInfo where T_Book.PK_TagInfo=T_TagInfo.Id and T_TagInfo.TagName=@TagName", new SqlParameter("TagName", TagName));

        }

        public static void getBookById(int Id, out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select T_Book.BookName,T_Book.BookUrl,T_Book.BookDescription,T_Book.PK_TagInfo,T_TagInfo.TagName,T_Book.UploadTime from T_Book,T_TagInfo where T_Book.PK_TagInfo=T_TagInfo.Id and T_Book.Id=@Id", new SqlParameter("Id", Id));
        }

        public static byte[] getBookImageById(int Id)
        {
            return (byte[])SQLHelper.ExecuteScalar("select BookImage from T_Book where Id=@Id", new SqlParameter("Id", Id));
        }



        //课程//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        public static void getCourseByShoolId(int PK_School, out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select * from T_Course where PK_School=@PK_School", new SqlParameter("PK_School", PK_School));
        }

        public static int deleteCourseById(int Id)
        {
            return SQLHelper.ExecuteNonQuery("delete from T_Course where Id=@Id", new SqlParameter("Id", Id));
        }

        public static void getCourseById(int Id, out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select * from T_Course where Id=@Id", new SqlParameter("Id", Id));
        }

        public static int updateCourseById(int Id, string CourseName, int PK_TagInfo, int CourseCapability, int CoursePrice, string Address)
        {
            return SQLHelper.ExecuteNonQuery("update T_Course set CourseName=@CourseName,PK_TagInfo=@PK_TagInfo,CourseCapability=@CourseCapability,CoursePrice=@CoursePrice,Address=@Address where Id=@Id", new SqlParameter("CourseName", CourseName), new SqlParameter("PK_TagInfo", PK_TagInfo), new SqlParameter("CourseCapability", CourseCapability), new SqlParameter("CoursePrice", CoursePrice), new SqlParameter("Address", Address), new SqlParameter("Id", Id));
        }

        public static int AddCourse(string CourseName, int PK_TagInfo, int CourseCapability, int CoursePrice, string Address, int PK_School)
        {
            return SQLHelper.ExecuteNonQuery("insert into T_Course(CourseName,PK_TagInfo,CourseCapability,CoursePrice,Address,PK_School)values(@CourseName,@PK_TagInfo,@CourseCapability,@CoursePrice,@Address,@PK_School)", new SqlParameter("CourseName", CourseName), new SqlParameter("PK_TagInfo", PK_TagInfo), new SqlParameter("CourseCapability", CourseCapability), new SqlParameter("CoursePrice", CoursePrice), new SqlParameter("Address", Address), new SqlParameter("PK_School", PK_School));
        }

        public static void getTopCourseBySchoolId(int PK_School, out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select * from T_Course where PK_School=@PK_School", new SqlParameter("PK_School", PK_School));
        }

        public static int AddSchoolPopularityOnePointBySchoolId(int Id)
        {
            return SQLHelper.ExecuteNonQuery("update T_School set Popularity=Popularity+1 where Id=@Id", new SqlParameter("Id", Id));
        }

        //评论///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

        public static int AddComment(string Comment, float Quality, float Price, float Service, float Environment, int PK_Account, int PK_School, string UploadTime)
        {
            int iresult = SQLHelper.ExecuteNonQuery("insert into T_Comment(Comment,SL_Quality,SL_Price,SL_Service,SL_Environment,PK_Account,PK_School,UploadTime)values(@Comment,@SL_Quality,@SL_Price,@SL_Service,@SL_Environment,@PK_Account,@PK_School,@UploadTime)", new SqlParameter("Comment", Comment), new SqlParameter("SL_Quality", Quality), new SqlParameter("SL_Price", Price), new SqlParameter("SL_Service", Service), new SqlParameter("SL_Environment", Environment), new SqlParameter("PK_Account", PK_Account), new SqlParameter("PK_School", PK_School), new SqlParameter("UploadTime", UploadTime));
            if (iresult == 1)
            {
                DataTable dt = new DataTable();
                dt = SQLHelper.ExecuteDataTable("select sum(SL_Quality),SUM(SL_Price),sum(SL_Service),sum(SL_Environment),COUNT(*) from T_Comment where PK_School=@Id", new SqlParameter("Id", PK_School));
                float iCount = float.Parse(dt.Rows[0][4].ToString());
                float fQuality = float.Parse(dt.Rows[0][0].ToString()) / iCount;
                float fPrice = float.Parse(dt.Rows[0][1].ToString()) / iCount;
                float fService = float.Parse(dt.Rows[0][2].ToString()) / iCount;
                float fEnvironment = float.Parse(dt.Rows[0][3].ToString()) / iCount;
                float fIntegrate = (float)((fQuality + fPrice + fService + fEnvironment) / 4.0);


                SQLHelper.ExecuteNonQuery("update T_School set SL_Quality=@fQuality,SL_Price=@fPrice,SL_Service=@fService,SL_Environment=@fEnvironment,SL_Integrate=@fIntegrate where Id=@Id", new SqlParameter("fQuality", fQuality), new SqlParameter("fPrice", fPrice), new SqlParameter("fService", fService), new SqlParameter("fEnvironment", fEnvironment),new SqlParameter("fIntegrate",fIntegrate), new SqlParameter("Id", PK_School));
                return iresult;
            }
            else
                return -1;
        }

        public static void getCommentBySchoolId(int Id, out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select T_Comment.Comment,T_Comment.SL_Quality,T_Comment.SL_Price,T_Comment.SL_Service,T_Comment.SL_Environment,T_Account.UserName,T_Comment.UploadTime from T_Account,T_Comment where T_Comment.PK_School=@Id and T_Comment.PK_Account=T_Account.Id", new SqlParameter("Id", Id));

        }

        //Favourite(收藏)////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

        public static int AddFavouriteSchoolToAccountId(int PK_Account, int PK_School)
        {
            return SQLHelper.ExecuteNonQuery("insert into T_Favourite(PK_Account,PK_School)values(@PK_Account,@PK_School)", new SqlParameter("PK_Account", PK_Account), new SqlParameter("@PK_School", PK_School));
        }

        public static void GetFavouriteSchoolsByAccountId(int PK_Account, out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select T_School.Id,T_School.Name,T_School.Address from T_School,T_Favourite where T_Favourite.PK_Account=@PK_Account and T_Favourite.PK_School=T_School.Id", new SqlParameter("PK_Account", PK_Account));
        }

        public static int DeleteFavouriteSchool(int PK_Account, int PK_School)
        {
            return SQLHelper.ExecuteNonQuery("delete from T_Favourite where PK_Account=@PK_Account and PK_School=@PK_School", new SqlParameter("PK_Account", PK_Account), new SqlParameter("PK_School", PK_School));
        }

        //Search//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

        public static void SearchSchool(Entity.MapPoint mp, out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select T_School.Id,T_School.Name,T_School.Address,T_School.Longitude,T_School.Latitude,T_School.BriefInfo,round(T_School.SL_Integrate,0) as SL_Integrate,round(T_School.SL_Quality,0) as SL_Quality,round(T_School.SL_Price,0) as SL_Price,round(T_School.SL_Service,0) as SL_Service,round(T_School.SL_Environment,0) as SL_Environment,T_School.Popularity,0 as Distance,(select COUNT(T_Comment.PK_School) from T_Comment where T_Comment.PK_School =T_School.Id) as CommentCount from T_School where T_School.Longitude<@fLo+0.01 and T_School.Longitude>@fLo-0.01 and T_School.Latitude<@fLa+0.01 and T_School.Latitude>@fLa-0.01", new SqlParameter("@fLo", mp.fLongitude), new SqlParameter("@fLa", mp.fLatitude));
            for (int ix = 0; ix < dt.Rows.Count; ix++)
            {
                dt.Rows[ix]["Distance"] = GeoUtils.DistanceOfTwoPoints((double)dt.Rows[ix]["Longitude"], (double)dt.Rows[ix]["Latitude"], (double)mp.fLongitude, (double)mp.fLatitude);
            }
            dt.DefaultView.Sort = "Distance ASC";
            dt = dt.DefaultView.ToTable();
        }

        public static void SearchSchoolByTag(Entity.MapPoint mp, int TagId, out DataTable dt)
        {

            dt = SQLHelper.ExecuteDataTable("select T_School.Id,T_School.Name,T_School.Address,T_School.Longitude,T_School.Latitude,T_School.BriefInfo,round(T_School.SL_Integrate,0) as SL_Integrate,round(T_School.SL_Quality,0) as SL_Quality,round(T_School.SL_Price,0) as SL_Price,round(T_School.SL_Service,0) as SL_Service,round(T_School.SL_Environment,0) as SL_Environment,T_School.Popularity,0 as Distance,(select COUNT(T_Comment.PK_School) from T_Comment where T_Comment.PK_School =T_School.Id) as CommentCount from T_School,T_Tag where T_Tag.TagId=@TagId and T_Tag.SchoolId=T_School.Id and T_School.Longitude<@fLo+0.01 and T_School.Longitude>@fLo-0.01 and T_School.Latitude<@fLa+0.01 and T_School.Latitude>@fLa-0.01", new SqlParameter("@fLo", mp.fLongitude), new SqlParameter("@fLa", mp.fLatitude), new SqlParameter("TagId", TagId));
            for (int ix = 0; ix < dt.Rows.Count; ix++)
            {
                dt.Rows[ix]["Distance"] = GeoUtils.DistanceOfTwoPoints((double)dt.Rows[ix]["Longitude"], (double)dt.Rows[ix]["Latitude"], (double)mp.fLongitude, (double)mp.fLatitude);
            }
            dt.DefaultView.Sort = "Distance ASC";
            dt = dt.DefaultView.ToTable();
        }


        //////////////////////////
        public static void getSchoolAccountInfoBySchoolId(int PK_School,out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select T_Account.UserName,T_Account.Password from T_Account,T_SchoolAccountInfo where T_Account.Id=T_SchoolAccountInfo.PK_Account and T_SchoolAccountInfo.PK_School=@PK_School", new SqlParameter("PK_School", PK_School));
        }

        public static int BindSchoolAccountToSchoolId(int PK_Account, int PK_School)
        {
            return SQLHelper.ExecuteNonQuery("update T_SchoolAccountInfo set PK_School=@PK_School where PK_Account=@PK_Account", new SqlParameter("PK_School", PK_School), new SqlParameter("PK_Account", PK_Account));
        }

        public static int getSchoolAccountIdByUserName(string UserName)
        {
            return (int)SQLHelper.ExecuteScalar("select Id From T_Account where UserName=@UserName", new SqlParameter("UserName", UserName));
        }

        public static int getSchoolIdBySchoolAccountId(int PK_Account)
        {
            object obj = SQLHelper.ExecuteScalar("select PK_School From T_SchoolAccountInfo where PK_Account=@PK_Account", new SqlParameter("PK_Account", PK_Account));
            if (obj is DBNull)
            {
                return -1;
            }
            else
                return (int)obj;
        }


        //T_CorrectInfo
        public static int addCorrectInfo(int PK_School, bool Address, bool Phone, bool Position, bool Course)
        {
            return SQLHelper.ExecuteNonQuery("insert into T_CorrectInfo(PK_School,Address,Phone,Position,Course) values(@PK_School,@Address,@Phone,@Position,@Course)", new SqlParameter("PK_School", PK_School), new SqlParameter("Address", Address), new SqlParameter("Phone", Phone), new SqlParameter("Position", Position), new SqlParameter("Course", Course));
        }

        //T_NewSchool
        public static int addNewSchool(string Name, string Address)
        {
            return SQLHelper.ExecuteNonQuery("insert into T_NewSchool(Name,Address) values(@Name,@Address)", new SqlParameter("Name", Name), new SqlParameter("Address", Address));
        }

        //AboutInfo
        public static void getAboutInfoByTitle(string Title,out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select * from T_AboutInfo where Title=@Title", new SqlParameter("Title", Title));
        }

        public static void getAboutInfoTitles(out DataTable dt)
        {
            dt = SQLHelper.ExecuteDataTable("select Id,Title from T_AboutInfo");
        }

        public static int updateAboutInfoById(int Id, string AboutContent)
        {
            return SQLHelper.ExecuteNonQuery("update T_AboutInfo set AboutContent=@AboutContent where Id=@Id", new SqlParameter("AboutContent", AboutContent), new SqlParameter("Id", Id));
        }
    }
}